﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Web.UI.WebControls;
using System.Xml;
using SevenZip;
using OfficeOpenXml;
using OfficeOpenXml.Style;
namespace CRDWToolSet
{
    public partial class BIARVerify : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            Button btn = sender as Button;
            btn.Enabled = false;
            try
            {

                if (FileUpload1.HasFile)
                {
                    btExport.Visible = false;

                    if (!FileUpload1.FileName.ToLower().EndsWith("biar"))
                    {
                        Response.Write(" <script> alert( 'Please select a BIAR file!') </script> ");
                        return;
                    }

                    string BiarFileName = Server.MapPath("App_data") + @"\" + FileUpload1.FileName; ;
                    FileUpload1.SaveAs(BiarFileName);

                    string tmpfolder = UnZipBIAR(BiarFileName);
                    if (tmpfolder.Length == 0)
                    {
                        Response.Write(" <script> alert( 'I cannot recognize your BIAR file!') </script> ");
                        return;
                    }
                    Session.Add("XmlDocFileName", tmpfolder + @"\BusinessObjects.xml");

                    XmlDocument doc = new XmlDocument();

                    doc.Load(tmpfolder + @"\BusinessObjects.xml");

                    #region InfoObject Type Dictionary
                    SortedDictionary<string, int> objectTypeDict = new SortedDictionary<string, int>();

                    foreach (XmlNode oneNode in doc.DocumentElement.ChildNodes)
                    {
                        string objectType = (oneNode.Attributes.GetNamedItem("xsi:type").Value);
                        int val = 0;
                        if (objectTypeDict.TryGetValue(objectType, out val))
                        {
                            objectTypeDict[objectType] = val + 1;
                        }
                        else
                        {
                            objectTypeDict.Add(objectType, 1);
                        }
                    }

                    Session.Add("objectTypeDict", objectTypeDict);
                    #endregion

                    SortedDictionary<string, string> filePropertiesDict = new SortedDictionary<string, string>();
                    FileInfo fi = new FileInfo(BiarFileName);
                    filePropertiesDict.Add("File Name",fi.Name);
                    filePropertiesDict.Add("File Size(K)",(fi.Length/1024).ToString());
                    filePropertiesDict.Add("MD5 Checksum", GetMD5HashFromFile(fi.FullName));

                    Session.Add("FilePropertiesDict", filePropertiesDict);
                    doc.Clone();

                    #region File Properties

                    tableFileProperties.Rows.Clear();
                    tableFileProperties.BorderWidth = 1;
                    TableRow tr;
                    TableCell tc;
                    foreach (KeyValuePair<string,string> keyValue in filePropertiesDict)
                    {
                        tr = new TableRow();

                        tc = new TableCell();
                        tc.Text = keyValue.Key;
                        tc.BorderWidth = 1;
                        tr.Cells.Add(tc);

                        tc = new TableCell();
                        tc.Text = keyValue.Value;
                        tc.BorderWidth = 1;
                        tr.Cells.Add(tc);

                        tableFileProperties.Rows.Add(tr);
                    }

                    #endregion

                    #region Package Summary Infomation
                    tablePkgInfo.BorderWidth = 1;
                    tablePkgInfo.Rows.Clear();
                    foreach (KeyValuePair<string, int> item in objectTypeDict)
                    {
                        tr = new TableRow();

                        tc = new TableCell();
                        tc.BorderWidth = 1;
                        tc.Text = item.Key.Split(':')[1];
                        tr.Cells.Add(tc);

                        tc = new TableCell();
                        tc.BorderWidth = 1;
                        tc.Text = item.Value.ToString();
                        tr.Cells.Add(tc);

                        tablePkgInfo.Rows.Add(tr);
                    }
                    #endregion

                    btExport.Visible = true;

                }
                else
                {
                    btExport.Visible = false;
                }

            }
            finally
            {
                btn.Enabled = true;
            }

        }

        protected string UnZipBIAR(string BiarFileName)
        {
            string tmpfolder = "";
            try
            {
                SevenZipExtractor.SetLibraryPath(Server.MapPath("DllLib") + @"\7z.dll");

                SevenZip.SevenZipExtractor unzip = new SevenZipExtractor(BiarFileName);

                tmpfolder = Server.MapPath("Temp") + @"\" + Guid.NewGuid().ToString();
                Directory.CreateDirectory(tmpfolder);
                for (int i = 0; i < unzip.ArchiveFileData.Count; i++)
                {
                    unzip.ExtractFiles(tmpfolder, unzip.ArchiveFileData[i].Index);
                }
            }
            catch { tmpfolder = ""; }
            //catch (SevenZipException ex)
            //{
            //    Response.Write(" <script> alert( '" + ex.Message + "') </script> ");
            //    tmpfolder = "";
            //}
            return tmpfolder;
        }

        protected string GetMD5HashFromFile(string fileName)
        {
            FileStream file = new FileStream(fileName, FileMode.Open);
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] retVal = md5.ComputeHash(file);
            file.Close();

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < retVal.Length; i++)
            {
                sb.Append(retVal[i].ToString("x2"));
            }
            return sb.ToString();
        }

        protected void btExport_Click(object sender, EventArgs e)
        {
            Button btn = sender as Button;
            btn.Enabled = false;

            try
            {
                SortedDictionary<string, int> objectTypeDict = Session["objectTypeDict"] as SortedDictionary<string, int>;
                SortedDictionary<string, string> filePropertiesDict = Session["FilePropertiesDict"] as SortedDictionary<string, string>;
                #region XML Name Space
                XmlDocument doc = new XmlDocument();
                doc.Load(Session["XmlDocFileName"].ToString());



                XmlNamespaceManager nsmgr = new XmlNamespaceManager(new XmlDocument().NameTable);
                foreach (XmlAttribute attribute in doc.DocumentElement.Attributes)
                {
                    if (attribute.Name.StartsWith("xmlns"))
                    {
                        nsmgr.AddNamespace(attribute.Name.Split(':')[1], attribute.Value);
                    }
                }
                #endregion

                #region Generate Excel Document

                //string outputExcelFileName = Server.MapPath("Output") + @"\" + Session["BiarFileName"].ToString().Replace("biar", "xlsx");// @"\BIARVerify" + DateTime.Now.ToString("MMddHHmmss") + @".xlsx"; //;

                //FileInfo newFile = new FileInfo(outputExcelFileName);
                //if (newFile.Exists)
                //{
                //    newFile.Delete();
                //    newFile = new FileInfo(outputExcelFileName);
                //}
                //using (ExcelPackage xlPackage = new ExcelPackage(newFile))
                using (ExcelPackage xlPackage = new ExcelPackage())
                {
                    int row = 2;
                    int column = 2;
                    ExcelWorksheet worksheet;
                    string objectTypeName = "";
                    ExcelRange range;

                    #region Summary Sheet
                    worksheet = xlPackage.Workbook.Worksheets.Add("Summary");

                    range = worksheet.Cells[1, 1, 1, 2];
                    range.Merge = true;
                    range.Value = "BIAR File Properties";
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                    range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                    foreach (KeyValuePair<string, string> item in filePropertiesDict)
                    {
                        worksheet.Cells[row, 1].Value = item.Key;
                        if (item.Key=="File Size(K)")
                        {
                        worksheet.Cells[row, 2].Value =Convert.ToInt32( item.Value);
                        }
                        else
                        {
                            worksheet.Cells[row, 2].Value = item.Value;
                        }
                        row++;

                    }

                    range = worksheet.Cells[2,1,row-1,2];

                    range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                    range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

                    row++;
                    int pkgInfoStartRow = row;
                    range = worksheet.Cells[row, 1, row, 2];
                    range.Merge = true;
                    range.Value = "BIAR Summary Information";
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    row++;

                    worksheet.Cells[row, 1].Value = "Object Type";
                    worksheet.Cells[row, 2].Value = "Count";

                    range=worksheet.Cells[row-1,1,row,2];
                    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                    range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                    row++;

                    foreach (KeyValuePair<string, int> item in objectTypeDict)
                    {
                        objectTypeName = item.Key.Split(':')[1];
                        worksheet.Cells[row, 1].Value = objectTypeName;
                        worksheet.Cells[row, 2].Value = item.Value;
                        worksheet.Cells[row, 1].Hyperlink = new ExcelHyperLink(objectTypeName + @"!A1", objectTypeName); ;
                        row++;

                    }
                    worksheet.Cells[row, 1].Value = "Total";
                    worksheet.Cells[row, 2].Formula = "sum(B2:B" + (row - 1).ToString() + ")";

                    range = worksheet.Cells[row, 1, row, 2];
                    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                    range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                    range = worksheet.Cells[pkgInfoStartRow+2,1,row-1,1];
                    range.Style.Font.UnderLine = true;
                    range.Style.Font.Color.SetColor(System.Drawing.Color.Blue);

                    range = worksheet.Cells[pkgInfoStartRow,1,row-1,2];

                    range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                    range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                    range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

                    worksheet.Column(1).AutoFit();
                    worksheet.Column(2).AutoFit();
                    #endregion

                    #region Detail Sheet
                    foreach (KeyValuePair<string, int> item in objectTypeDict)
                    {
                        worksheet = xlPackage.Workbook.Worksheets.Add(item.Key.Split(':')[1]);
                        row = 2;
                        column = 1;

                        objectTypeName = item.Key.Split(':')[1];

                        string parentCuid;
                        XmlNode parentNode;

                        switch (objectTypeName)
                        {
                            case "CrystalReport":
                                #region Crystal Report

                                worksheet.Cells[1, 1].Value = "Report Name";
                                worksheet.Cells[1, 2].Value = "Report ID";
                                worksheet.Cells[1, 3].Value = "Parent Folder";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    //report name
                                    worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //report id
                                    try { worksheet.Cells[row, 2].Value = node.SelectSingleNode("io:Description", nsmgr).InnerText.Split('|')[0].Split(':')[1].Trim(); }
                                    catch { worksheet.Cells[row, 2].Value = ""; }
                                    //parent folder
                                    parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                    parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                    worksheet.Cells[row, 3].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    row++;
                                }
                                column = 3;
                                #endregion
                                break;
                            case "Webi":
                                #region Webi Report

                                worksheet.Cells[1, 1].Value = "Report Name";
                                worksheet.Cells[1, 2].Value = "Report ID";
                                worksheet.Cells[1, 3].Value = "Parent Folder";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    //report name
                                    worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //report id
                                    try { worksheet.Cells[row, 2].Value = node.SelectSingleNode("io:Description", nsmgr).InnerText.Split('|')[0].Split(':')[1].Trim(); }
                                    catch { worksheet.Cells[row, 2].Value = ""; }
                                    //parent folder
                                    parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                    parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                    worksheet.Cells[row, 3].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    row++;
                                }
                                column = 3;
                                #endregion
                                break;
                            case "Folder":
                                #region Folder
                                worksheet.Cells[1, 1].Value = "Folder Name";
                                worksheet.Cells[1, 2].Value = "Parent Folder Name";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //parent folder
                                    try
                                    {
                                        parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                        parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                        worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    }
                                    catch (NullReferenceException)
                                    {
                                        worksheet.Cells[row, 2].Value = "";
                                    }

                                    row++;
                                }
                                column = 2;
                                #endregion
                                break;
                            case "MetaData.DataDBField":
                                #region MetaData.DataDBField
                                worksheet.Cells[1, 1].Value = "Field Name";
                                worksheet.Cells[1, 2].Value = "Parent Table";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    //report name
                                    worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //Parent Table
                                    parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                    parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                    worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    row++;
                                }
                                column = 2;
                                #endregion
                                break;
                            case "MetaData.DataCommandTable":
                                #region MetaData.DataCommandTable
                                worksheet.Cells[1, 1].Value = "Command Table Name";
                                worksheet.Cells[1, 2].Value = "Parent Name";
                                worksheet.Cells[1, 3].Value = "Command Text";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    //command table name
                                    worksheet.Cells[row, 1].Value = node.SelectNodes("io:Name", nsmgr)[0].InnerText;
                                    //Parent Table
                                    parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                    parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                    worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //command text
                                    worksheet.Cells[row, 3].Value = node.SelectSingleNode("md.dct:MetadataProperties", nsmgr).SelectSingleNode("md.dct:CommandText", nsmgr).InnerText;
                                    row++;
                                }
                                column = 3;
                                #endregion
                                break;
                            case "MetaData.BusinessFormulaField":
                                #region MetaData.BusinessFormulaField
                                worksheet.Cells[1, 1].Value = "Formula Field Name";
                                worksheet.Cells[1, 2].Value = "Parent Table";
                                worksheet.Cells[1, 3].Value = "Formula Text";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    //report name
                                    worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //Parent Table
                                    parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                    parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                    worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    //Formula Text
                                    worksheet.Cells[row, 3].Value = node.SelectSingleNode("md.ff:MetadataProperties", nsmgr).SelectSingleNode("md.ff:FormulaText", nsmgr).InnerText;
                                    row++;
                                }
                                column = 3;
                                #endregion
                                break;
                            default:
                                #region Others
                                worksheet.Cells[1, 1].Value = "Object Name";
                                worksheet.Cells[1, 2].Value = "Parent Object Name";
                                foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                {
                                    worksheet.Cells[row, 1].Value = node.SelectNodes("io:Name", nsmgr)[0].InnerText;
                                    //parent object 
                                    try
                                    {
                                        parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                        parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                        worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                    }
                                    catch (NullReferenceException)
                                    {
                                        worksheet.Cells[row, 2].Value = "";
                                    }
                                    row++;
                                }
                                column = 2;
                                #endregion
                                break;
                        }


                        #region Adjust column Width
                        for (int i = 1; i <= column; i++)
                        {
                            worksheet.Column(i).AutoFit();
                        }
                        #endregion

                        #region Column Header Font and Background
                        range = worksheet.Cells[1, 1, 1, column];
                        range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                        range.Style.Font.Color.SetColor(System.Drawing.Color.White);
                        #endregion

                        #region Cell Border
                        range = worksheet.Cells[2, 1, row - 1, column];
                        range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                        range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                        range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                        range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                        range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                        range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                        range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                        range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                        #endregion
                    }
                    #endregion

                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=" + filePropertiesDict["File Name"].Replace("biar", "xlsx"));
                    Response.BinaryWrite(xlPackage.GetAsByteArray());
                    Response.End();
                }
                #endregion

                //Response.AddHeader("Content-Disposition", "attachment; filename=" + newFile.Name);
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";// "Application/X-msexcel";
                //Response.WriteFile(newFile.FullName);
                //Response.End();
            }
            finally
            {
                btn.Enabled = true;
            }


        }
    }
}